﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Objects;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using ALinq.Dynamic;
using NorthwindDemo;

namespace ALinq.Dynamic.Test
{


    [TestClass]
    public class EntitySqlTest : BaseTest
    {
        #region 文字
        [TestMethod]
        public void IntegerLiteral()
        {
            string esql;

            esql = "123";
            var int32 = db.CreateQuery<int>(esql).Single();
            Console.WriteLine(int32);

            esql = "123u";
            var uint32 = db.CreateQuery<uint>(esql).Single();
            Console.WriteLine(uint32);

            esql = "123l";
            var int64 = db.CreateQuery<long>(esql).Single();
            Console.WriteLine(int64);

            esql = "123ul";
            var uint64 = db.CreateQuery<ulong>(esql).Single();
            Console.WriteLine(uint64);

        }

        [TestMethod]
        public void StringLiteral()
        {
            var esql = "'hello'";
            var text = db.CreateQuery<string>(esql).Single();
            Console.WriteLine(text);

            esql = "\"hello\"";
            text = db.CreateQuery<string>(esql).Single();
            Console.WriteLine(text);
        }

        [TestMethod]
        public void NullLiteral()
        {
            var esql = "null";
            var obj = db.CreateQuery<object>(esql).Single();
            Assert.AreEqual(null, obj);
        }

        [TestMethod]
        public void BooleanLiteral()
        {
            string esql;
            bool obj;

            esql = "true";
            obj = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, obj);

            esql = "false";
            obj = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, obj);
        }

        [TestMethod]
        public void DateTimeLiteral()
        {

            var esql = "#2006-12-25 01:01#";
            var datetime = db.CreateQuery<DateTime>(esql).Single();
            Console.WriteLine(datetime);

            var expected = new DateTime(2006, 12, 25, 1, 1, 0);
            Assert.AreEqual(expected, datetime);

            esql = "DATETIME '2006-12-25 01:01'";
            datetime = db.CreateQuery<DateTime>(esql).Single();
            Console.WriteLine(datetime);

            Assert.AreEqual(expected, datetime);

        }

        [TestMethod]
        public void GuidLiteral()
        {
            string esql;

            esql = "Guid 'CD582A70-863B-4E4B-9530-174A83EEB0C0'";
            var guid = db.CreateQuery<Guid>(esql).Single();
            Console.WriteLine(guid);

            var expected = new Guid("CD582A70-863B-4E4B-9530-174A83EEB0C0"); //new DateTime(0, 0, 0, 13, 1, 8);
            Assert.AreEqual(expected, guid);


            //var b = new ALinq.Binary(new Byte[] {});

        }

        [TestMethod]
        public void BinaryLiteral()
        {
            string esql;

            esql = "Binary '00ffaabb'";
            var bytes = db.CreateQuery<byte[]>(esql).Single();
            Console.WriteLine(bytes.Length);

        }




        #endregion

        #region 自定义函数
        [TestMethod]
        public void CustomFunction1()
        {
            var q = db.Employees.Select(o => db.GetFullName(o.FirstName, o.LastName));

            var esql = "select value GetFullName(e.FirstName, e.LastName) from Employees as e";
            q = db.CreateQuery<string>(esql);
            var items = q.Execute();
            foreach (var item in items)
                Console.WriteLine(item);

            //items = db.Employees.Select("GetFullName(FirstName, LastName").Cast<string>();
            //foreach (var item in items)
            //    Console.WriteLine(item);
        }

        [TestMethod]
        public void CustomFunction2()
        {
            //Expression.MakeMemberAccess()
            var q = db.Employees.Select(o => db.GetFullName(o.FirstName, o.LastName));
            //GetFullName(e.FirstName, e.LastName),
            var esql = "select GetFullName(e.FirstName, e.LastName) as FullName from Employees as e";
            var items = db.CreateQuery<IDataRecord>(esql);
            foreach (var item in items)
                Console.WriteLine(item);
        }
        #endregion

        #region 构造函数
        [TestMethod]
        public void Constructor1()
        {
            var esql = "select value Person(e.EmployeeID){ e.FirstName, e.LastName } from Employees as e";
            var q1 = db.CreateQuery<NorthwindDemo.Person>(esql)
                       .Where(p => p.FirstName == "xxxx");

            q1.Execute();

        }


        [TestMethod]
        public void Constructor2()
        {
            var esql = @"using ALinq.Dynamic.Test;
                         select value Person2{ e.FirstName, e.LastName } 
                         from Employees as e";
            var q1 = db.CreateQuery<Person2>(esql).Where(o => o.LastName == "AAA");

            q1.Execute();

        }

        #endregion

        #region 聚合函数
        [TestMethod]
        public void Count()
        {
            string esql;
            esql = "select value count() from products as p";
            var count = db.CreateQuery<int>(esql).Execute();
        }

        [TestMethod]
        public void BigCount()
        {
            var esql = "select value bigcount() from products as p";
            var bigcount = db.CreateQuery<long>(esql).Execute();
            Console.WriteLine(bigcount);
        }

        [TestMethod]
        public void Avg()
        {
            string esql;
            esql = "select value avg(p.UnitPrice) from Products as p";
            var q1 = db.CreateQuery<decimal?>(esql);
            foreach (var item in q1)
                Console.WriteLine(item);

            var value = db.CreateQuery<decimal?>(esql).Single();
            Console.WriteLine(value);
        }

        [TestMethod]
        public void Min1()
        {
            string esql;
            esql = "select value min(p.UnitPrice) from Products as p";
            var q1 = db.CreateQuery<decimal?>(esql);
            foreach (var item in q1)
                Console.WriteLine(item);

            var value = db.CreateQuery<decimal?>(esql).Single();
            Console.WriteLine(value);
        }

        [TestMethod]
        public void Min2()
        {
            string esql;
            esql = "select min(p.UnitPrice) from Products as p";
            var q2 = db.CreateQuery<IDataRecord>(esql);
            foreach (var item in q2)
                Console.WriteLine(item[0]);
        }

        [TestMethod]
        public void Min3()
        {
            string esql;
            esql = "select value min(p.UnitPrice) from Products as p group by p.CategoryId";
            var q1 = db.CreateQuery<decimal?>(esql);
            foreach (var item in q1)
                Console.WriteLine(item);
        }

        [TestMethod]
        public void Min4()
        {
            //测试延迟加载
            var sb = new StringBuilder();
            var writer = new StringWriter(sb);
            db.Log = writer;

            string esql;
            esql = "select min(p.UnitPrice) from Products as p";

            //确定 Log 生效
            sb.Length = 0;
            db.CreateQuery<IDataRecord>(esql).Execute();
            db.Log.Flush();
            Assert.AreNotEqual(0, sb.Length);


            sb.Length = 0;
            db.CreateQuery<IDataRecord>(esql);
            db.Log.Flush();
            Assert.AreEqual(0, sb.Length);

        }


        [TestMethod]
        public void Sum1()
        {
            var q = db.OrderDetails.Sum(o => o.UnitPrice);
            string esql;
            esql = "select value sum(o.UnitPrice) from OrderDetails as o";
            var items = db.CreateQuery<decimal>(esql);
            foreach (var item in items)
                Console.WriteLine(item);
        }

        [TestMethod]
        public void Sum2()
        {
            var q = db.OrderDetails.Sum(o => o.UnitPrice);
            string esql;
            esql = "select key, sum(o.Quantity) as SumQuantity  from OrderDetails as o group by o.Product as key";
            var items = db.CreateQuery<IDataRecord>(esql);
            foreach (var item in items)
            {
                Console.WriteLine(item);
            }
        }

        [TestMethod]
        public void Sum3()
        {
            string esql;
            esql = "select value sum(o.Quantity)  from OrderDetails as o group by o.ProductId";
            var items = db.CreateQuery<int>(esql);
            foreach (var item in items)
            {
                Console.WriteLine(item);
            }
        }


        #endregion

        #region 操作符
        [TestMethod]
        public void Add()
        {
            //数值相加
            string esql;
            object value;

            esql = "10 + 5";
            value = db.CreateQuery<int>(esql).Single();
            Assert.AreEqual(15, value);
        }

        [TestMethod]
        public void StringContact()
        {
            //字符串连接
            string esql;
            object value;

            esql = "'hello' + 'world'";
            value = db.CreateQuery<string>(esql).Single();
            Assert.AreEqual("helloworld", value);

            esql = "\"hello\" + \"world\"";
            value = db.CreateQuery<string>(esql).Single();
            Assert.AreEqual("helloworld", value);
        }

        [TestMethod]
        public void Negative()
        {
            //负号
            string esql;
            object value;

            esql = "-99";
            value = db.CreateQuery<int>(esql).Single();
            Assert.AreEqual(-99, value);
        }

        [TestMethod]
        public void Subtract()
        {
            // 相减
            string esql;
            object value;

            esql = "100-99";
            value = db.CreateQuery<int>(esql).Single();
            Assert.AreEqual(1, value);
        }

        [TestMethod]
        public void Multiply()
        {
            //相乘
            string esql;
            object value;

            esql = "3 * 5";
            value = db.CreateQuery<int>(esql).Single();
            Assert.AreEqual(15, value);
        }

        [TestMethod]
        public void Divide()
        {
            //除
            string esql;
            object value;

            esql = "9 / 3";
            value = db.CreateQuery<int>(esql).Single();
            Assert.AreEqual(3, value);
        }

        [TestMethod]
        public void Modulo()
        {
            //除模
            var esql = "10 % 3";
            var value = db.CreateQuery<int>(esql).Single();
            Console.WriteLine(value);
        }

        [TestMethod]
        public void And()
        {
            //&& and
            string esql;
            bool value;

            esql = "true && true";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);

            esql = "true && false";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);

            esql = "true and true";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);

            esql = "true and false";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);
        }

        [TestMethod]
        public void Or()
        {
            //or || 
            string esql;
            bool value;

            esql = "true || false";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);

            esql = "false || false";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);

            esql = "true or false";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);

            esql = "false or false";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);
        }

        [TestMethod]
        public void Not()
        {
            //not !
            string esql;
            bool value;

            esql = "!false";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);

            esql = "!true";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);

            esql = "not false";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);

            esql = "not true";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);
        }

        [TestMethod]
        public void Equal()
        {
            // = ==
            var esql = "10 == 5";
            var value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);

            esql = "10 == 10";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);

            esql = "10 = 5";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);

            esql = "10 =10";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);
        }

        [TestMethod]
        public void Greater()
        {
            // >
            var esql = "10 > 5";
            var value = db.CreateQuery<bool>(esql).Single();
            Console.WriteLine(value);

            esql = "5 > 10";
            value = db.CreateQuery<bool>(esql, new ObjectParameter("p1", 5)).Single();
            Console.WriteLine(value);
        }

        [TestMethod]
        public void GreaterEqual()
        {
            // >=
            var esql = "10 >= 5";
            var value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);

            esql = "5 >= 10";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);
        }

        [TestMethod]
        public void Less()
        {
            var esql = "10 < 5";
            var value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);

            esql = "5 < 10";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);
        }

        [TestMethod]
        public void LessEqual()
        {
            var esql = "10 <= 5";
            var value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);

            esql = "10 >= 5";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);
        }

        [TestMethod]
        public void NotEqual()
        {
            // !=
            var esql = "10 != 5";
            var value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);

            esql = "10 != 10";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);

            esql = "10 <> 5";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);

            esql = "10 <> 10";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);
        }

        [TestMethod]
        public void MemberVisit()
        {
            // .
        }

        [TestMethod]
        public void Comment()
        {
            // --
        }



        #endregion

        #region 数学函数
        [TestMethod]
        public void Abs()
        {
            var esql = "Abs(-10)";
            var value = db.CreateQuery<int>(esql).Single();
            Assert.AreEqual(10, value);

            esql = "Abs(10)";
            value = db.CreateQuery<int>(esql).Single();
            Assert.AreEqual(10, value);

        }

        [TestMethod]
        public void Ceiling()
        {
            var esql = "Ceiling(12.4)";
            var value = db.CreateQuery<int>(esql).Single();
            Assert.AreEqual(13, value);
        }

        [TestMethod]
        public void Floor()
        {
            var esql = "Floor(13.4)";
            var value = db.CreateQuery<object>(esql).Single();

            Assert.AreEqual(Math.Floor(13.4), value);
        }

        [TestMethod]
        public void Power()
        {
            var esql = "Power(133, 4)";
            var value = db.CreateQuery<object>(esql).Single();

            Assert.AreEqual(Math.Pow(133, 4), value);
        }

        [TestMethod]
        public void Round()
        {
            var esql = "Round(748.58)";
            var value = db.CreateQuery<object>(esql).Single();

            Assert.AreEqual(Math.Round(748.58), value);

            esql = "Round(748.58,1)";
            value = db.CreateQuery<object>(esql).Single();

            Assert.AreEqual(Math.Round(748.58, 1), value);
        }

        [TestMethod]
        public void Truncate()
        {
            var digits = 0;
            var dec = 5 / Math.Pow(10, digits + 1);
            Console.WriteLine(dec);
            var value1 = Math.Round(748.59 + dec, digits) - dec - dec;
            Console.WriteLine(value1);

            var value2 = Math.Round(748.55 + dec, digits) - dec - dec;
            Console.WriteLine(value2);

            var value3 = Math.Round(748.54 + dec, digits) - dec - dec;
            Console.WriteLine(value3);

            var value4 = Math.Round(748.51 + dec, digits) - dec - dec;
            Console.WriteLine(value4);

            var value5 = Math.Round(748.50 + dec, digits) - dec - dec;
            Console.WriteLine(value5);

            //var esql = "Truncate(748.58, 1)";
            //var value = db.CreateQuery<object>(esql).Single();

            //Assert.AreEqual(Math.Truncate(748.58), value);

        }

        [TestMethod]
        public void PI()
        {
            var esql = "pi()";
            var value = db.CreateQuery<double>(esql).Single();

            Assert.IsTrue(value > 3.14);

        }


        #endregion

        #region 字符串函数
        [TestMethod]
        public void Contact()
        {
            var esql = "Concat('abc','123')";
            var value = db.CreateQuery<string>(esql).Single();

            Assert.AreEqual("abc123", value);
        }

        [TestMethod]
        public void Contains()
        {
            var esql = "Contains('abc','123')";
            var value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);

            esql = "Contains('abc','ab')";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);
        }

        [TestMethod]
        public void EndsWith()
        {
            var esql = "EndsWith('abc','123')";
            var value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(false, value);

            esql = "EndsWith('abc','bc')";
            value = db.CreateQuery<bool>(esql).Single();
            Assert.AreEqual(true, value);
        }

        [TestMethod]
        public void IndexOf()
        {
            var esql = "IndexOf('abc','123')";
            var value = db.CreateQuery<int>(esql).Single();
            Assert.AreEqual(-1, value);


        }

        [TestMethod]
        public void Left()
        {
            var esql = "left('abcdef',3)";
            var value = db.CreateQuery<string>(esql).Single();
            Assert.AreEqual("abc", value);
        }

        [TestMethod]
        public void Right()
        {
            var esql = "right('abcxyz',3)";
            var value = db.CreateQuery<string>(esql).Single();
            Assert.AreEqual("xyz", value);
        }
        #endregion

        #region Keywords（关键字）

        [TestMethod]
        public void Between()
        {
            db.Products.Where(p => p.ProductID >= 10 && p.ProductID <= 100);

            string esql;
            esql = "select p from Products as p where p.ProductId Between 10 and 100";
            var q = db.CreateQuery<Product>(esql).Execute();

            esql = "select p from Products as p where p.ProductId not between 10 and 100";
            db.CreateQuery<Product>(esql).Execute();
        }

        [TestMethod]
        public void Cast()
        {
            string esql;

            //TODO: 支持 int, short, long 等类名

            esql = "select value cast(c.CategoryId as string) from Categories as c";
            db.CreateQuery<string>(esql).Execute();

            esql = "select value cast(c.CategoryId as int16) from Categories as c";
            db.CreateQuery<short>(esql).Execute();

            esql = "select value cast(c.CategoryId as int32) from Categories as c";
            db.CreateQuery<int>(esql).Execute();

            esql = "select value cast(c.CategoryId as int64) from Categories as c";
            db.CreateQuery<long>(esql).Execute();


        }

        //[TestMethod]
        //public void Collection()
        //{
        //    var esql = "select o from collection(Product) as o";
        //    ef.CreateQuery<Product>(esql).Execute(MergeOption.NoTracking);
        //}

        [TestMethod]
        public void Except1()
        {
            var esql = @"(select p1.ProductName from Products as p1 
                         where p1.CategoryID = 2)
                         except
                         (select p2.ProductName from Products as p2
                         where p2.CategoryID = 3)";

            db.CreateQuery<object>(esql).Execute();
        }

        [TestMethod]
        public void Except2()
        {
            var esql = @"(select value p1 from Products as p1 
                         where p1.CategoryID = 2)
                         except
                         (select value p2 from Products as p2
                         where p2.CategoryID = 3)";

            db.CreateQuery<object>(esql).Execute();
        }

        [TestMethod]
        public void Except3()
        {
            var esql = @"(select p1 from Products as p1 
                         where p1.CategoryID = 2)
                         except
                         (select p2 from Products as p2
                         where p2.CategoryID = 3)";

            db.CreateQuery<object>(esql).Execute();
        }

        [TestMethod]
        public void Exists()
        {
            var esql = @"select p from Products as p 
                         where exists(select p from Products as p where p.UnitPrice > 0)";
            var items = db.CreateQuery<Product>(esql).Execute();

            esql = @"select p from Products as p 
                         where not exists(select p from Products as p where p.UnitPrice > 0)";
            items = db.CreateQuery<Product>(esql).Execute();
        }

        [TestMethod]
        public void Flatten()
        {
            var esql = @"flatten(select value o.OrderDetails from Orders as o)";
            //var items = ef.CreateQuery<EF.OrderDetail>(esql).Execute(MergeOption.NoTracking).ToArray();
            //Console.WriteLine(items.Length);
        }

        [TestMethod]
        public void From()
        {
            var esql = @"select p from Products as p";
            var q = db.CreateQuery<Product>(esql).Execute();
        }

        [TestMethod]
        public void Group1()
        {
            string esql;
            IEnumerable<IDataRecord> items;
            esql = "select SupplierID from Products as p group by p.SupplierID";
            items = db.CreateQuery<IDataRecord>(esql).Execute();
            foreach (var item in items)
                Console.WriteLine(item["SupplierID"]);

            //db.Products.GroupBy(o => o.SupplierID).Select(a => a.Select(b => b));

            esql = "select p.SupplierID from Products as p group by p.SupplierID";
            items = db.CreateQuery<IDataRecord>(esql).Execute();
            foreach (var item in items)
                Console.WriteLine(item["SupplierID"]);
            //ef.CreateQuery<string>(esql).Execute(MergeOption.NoTracking);
        }

        [TestMethod]
        public void Group2()
        {
            db.Products.GroupBy(o => o.SupplierID).Select(o => new { name = o.Key });

            var esql = "select name from Products as p group by p.SupplierID as name";
            var q = db.CreateQuery<IDataRecord>(esql);
            foreach (var item in q)
                Console.WriteLine(item["name"]);
        }

        [TestMethod]
        public void Group3()
        {

            //var q = db.OrderDetails.GroupBy(c => c.ProductID).Select(o => o.Key);
            //db.OrderDetails.GroupBy(c => new { c.ProductID, c.UnitPrice }).ToArray();
            //return;

            string esql;
            esql = "select value ProductId from OrderDetails as c group by c.ProductId";
            var productKeys = db.CreateQuery<int>(esql);
            foreach (var item in productKeys)
                Console.WriteLine(item);

            esql = "select value c.ProductId from OrderDetails as c group by c.ProductId";
            productKeys = db.CreateQuery<int>(esql);
            foreach (var item in productKeys)
                Console.WriteLine(item);
        }

        [TestMethod]
        public void Group4()
        {
            //            var q = db.OrderDetails.GroupBy(o => o.Product)
            //                                   .Select(o => new { Product = o, SumQuantity = o.Sum(a => a.Quantity) });

            string esql;
            esql = @"select Product, sum(o.Quantity) as SumQuantity  
                                 from OrderDetails as o 
                                 group by o.Product";

            //ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking);

            db.CreateQuery<IDataRecord>(esql).Execute();


            esql = @"select o.Product, sum(o.Quantity) as SumQuantity  
                                     from OrderDetails as o group by o.Product";
            //ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking);

            db.CreateQuery<IDataRecord>(esql).Execute();



        }

        [TestMethod]
        public void Group5()
        {
            var esql = @"select d.OrderID from OrderDetails as d
                         where d.UnitPrice > 1000
                         group by d.OrderID";

            //ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking);
            db.CreateQuery<IDataRecord>(esql).Execute();
            //db.OrderDetails.GroupBy(d => d.OrderID).Select(o => new { o.Key });
        }

        [TestMethod]
        public void Group6()
        {
            string esql;
            esql = @"select OrderID, sum(o.Freight)
                     from Orders as o
                          inner join OrderDetails as d on o.OrderID = d.OrderID
                          inner join Products as p on d.ProductID = p.ProductID
                     group by o.OrderId";

            //ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking);
            db.CreateQuery<IDataRecord>(esql).Execute();
        }

        [TestMethod]
        public void Group7()
        {
            string esql;
            esql = @"select od.o.OrderID as OrderID
                     from ( select o, d 
                            from Orders as o 
                                 inner join OrderDetails as d on o.OrderID = d.OrderID ) as od
                     group by od.o.OrderID";

            //ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking);
            var q = db.CreateQuery<object>(esql).Execute();
        }

        [TestMethod]
        public void Group8()
        {
            string esql;
            esql = @"select o
                     from Orders as o
                          inner join OrderDetails as d on o.OrderID = d.OrderID
                     group by o";

            //ef.CreateQuery<object>(esql).Execute(MergeOption.NoTracking);
            var q = db.CreateQuery<object>(esql);
            q.Execute();

            db.Orders.Join(db.OrderDetails, o => o.OrderID, d => d.OrderID, (o, d) => new { o, d })
                     .GroupBy(od => od.o)
                     .Select(c => new { Order = c.Key, OrderDetails = c.Select(b => b.d) })
                     .ToArray();
        }


        [TestMethod]
        public void Group9()
        {
            string esql;
            esql = @"select Product, count(true)  
                     from OrderDetails as o 
                     group by o.Product";

            //ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking);
            db.CreateQuery<IDataRecord>(esql).Execute();
        }

        [TestMethod]

        public void GroupPartition1()
        {
            var q1 = db.Products.GroupBy(o => o.CategoryID).Select(o => new { o.Key, Products = o.Select(b => b) });
            var esql = @"select CategoryID, GroupPartition(p) as Products
                         from Products as p 
                         group by p.CategoryID";


            var items = db.CreateQuery<IDataRecord>(esql);
            foreach (var item in items)
            {
                foreach (Product p in (IEnumerable<Product>)item["Products"])
                {
                    Console.WriteLine(p.ProductName);
                }
            }


        }

        [TestMethod]
        public void GroupPartition2()
        {
            var q1 = db.Products.GroupBy(o => o.CategoryID).Select(o => new { o.Key, Products = o.Select(b => b) });
            //new ExpressionVisitor().Visit(q1.Expression);

            var esql = @"select CategoryID, GroupPartition(p.UnitPrice + 1) as ProductUnitPrices
                         from Products as p 
                         group by p.CategoryID";

            //ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking).ToArray();

            var items = db.CreateQuery<IDataRecord>(esql);
            foreach (var item in items)
            {
                foreach (decimal p in (IEnumerable<decimal?>)item["ProductUnitPrices"])
                {
                    Console.WriteLine(p);
                }
            }
        }

        [TestMethod]
        public void GroupPartition3()
        {
            string esql;
            esql = @"select od.o.OrderID as OrderID, GroupPartition(od.d) as OrderDetails
                     from ( select o, d 
                            from Orders as o 
                                 inner join OrderDetails as d on o.OrderID = d.OrderID ) as od
                     group by od.o.OrderID";

            //ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking).ToArray();
            var q = db.CreateQuery<IDataRecord>(esql);
            foreach (var item in q)
            {
                Console.WriteLine(item["OrderID"]);
                foreach (OrderDetail d in (IEnumerable<OrderDetail>)item["OrderDetails"])
                {
                    Console.WriteLine(d.ProductID);
                }
            }

            db.Orders.Join(db.OrderDetails, o => o.OrderID, d => d.OrderID, (o, d) => new { o, d })
                     .GroupBy(od => od.o.OrderID)
                     .Select(x => new { OrderID = x.Key, Ordetails = x.Select(y => y.d) });

        }

        [TestMethod]
        public void GroupPartition4()
        {
            string esql;
            esql = @"select od.o.OrderID as OrderID, 
                           (select top(1) o from GroupPartition(od.o) as o) as Order,
                            GroupPartition(od.d) as OrderDetails
                     from ( select o, d 
                            from Orders as o 
                                 inner join OrderDetails as d on o.OrderID = d.OrderID ) as od
                     group by od.o.OrderID";

            //ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking).ToArray();
            //var q = db.CreateQuery(esql).Cast<dynamic>();
            //foreach (var item in q)
            //{
            //    Console.WriteLine("=================== ORDER ====================");

            //    foreach(Order o in item.Orders)
            //    {
            //        Console.WriteLine(o.OrderID);
            //    }

            //    Console.WriteLine("================== ORDER DETAILS ================");

            //    foreach (OrderDetail d in item.OrderDetails)
            //    {
            //        Console.WriteLine(d.ProductID);
            //    }
            //    Console.WriteLine("==============================================");
            //}

            //db.Orders.Join(db.OrderDetails, o => o.OrderID, d => d.OrderID, (o, d) => new { o, d })
            //         .GroupBy(od => od.o.OrderID)
            //         .Select(x => new { OrderID = x.Key, Ordetails = x.Select(y => y.d) });

        }


        //        [TestMethod]
        //        public void GroupPartition2()
        //        {
        //            string esql;
        //            esql = @"select OrderID
        //                     from Orders as o
        //                          inner join OrderDetails as d on o.OrderID = d.OrderID
        //                     group by o.OrderId";

        //            ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking);
        //            db.CreateQuery<IDataRecord>(esql).Execute();

        //            esql = @"select od.o.OrderID as OrderID
        //                     from ( select o, d 
        //                            from Orders as o 
        //                                 inner join OrderDetails as d on o.OrderID = d.OrderID ) as od
        //                     group by od.o.OrderID";

        //            ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking);
        //            var q = db.CreateQuery<object>(esql).Execute();

        //            //db.Orders.Join(db.OrderDetails, a => a.OrderID, b => b.OrderID, (o, d) => new { o, d })
        //            //         .GroupBy(a => a.o.OrderID).ToArray();
        //        }

        [TestMethod]
        public void Having1()
        {
            var esql = @"select value c from Products as p 
                        group by p.CategoryId as c 
                        having c > 1000";

            //ef.CreateQuery<int>(esql).Execute(MergeOption.NoTracking);
            var item = db.CreateQuery<int>(esql).Execute();

        }

        [TestMethod]
        public void Having2()
        {
            var esql = @"select c from Products as p 
                        group by p.Category as c 
                        having c.CategoryId > 1000";
            var item = db.CreateQuery<Category>(esql).Execute();

        }

        [TestMethod]
        public void Having3()
        {
            var q = db.Products.GroupBy(o => o.CategoryID)
                                .Where(o => Enumerable.Max<Product>(o, a => a.UnitPrice) > 1000);

            var esql = @"select value c from Products as p 
                        group by p.CategoryId as c 
                        having max(p.UnitPrice) > 1000";

            //ef.CreateQuery<IDataRecord>(esql).Execute(MergeOption.NoTracking);
            var item = db.CreateQuery<int>(esql).Execute();
        }

        [TestMethod]
        public void Like()
        {
            //db.Products.Where(o => o.ProductName.Contains("aa")).Execute();
            //db.Products.Where(o => o.ProductName.StartsWith("aa")).Execute();
            //db.Products.Where(o => o.ProductName.EndsWith("aa")).Execute();
            string esql;
            IQueryable q;

            esql = @"select p from Products as p
                     where p.ProductName like '%aaa%'";
            q = db.CreateQuery(esql);
            q.Execute();

            esql = @"select p from Products as p
                     where p.ProductName like 'aaa'";
            q = db.CreateQuery(esql);
            q.Execute();


            esql = @"select p from Products as p
                     where p.ProductName like '%aaa'";
            q = db.CreateQuery(esql);
            q.Execute();

            esql = @"select p from Products as p
                     where p.ProductName like 'aaa%'";
            q = db.CreateQuery(esql);
            q.Execute();
        }

        [TestMethod]
        public void MultiSet1()
        {
            var esql = "MULTISET(1, 2, 3)";
            var q2 = db.CreateQuery<int>(esql);
            foreach (var item in q2)
                Console.WriteLine(item);

        }

        [TestMethod]
        public void MultiSet2()
        {
            new[] { 1, 2, 3 }.Select(o => o);

            string esql;
            IQueryable<int> q2;
            esql = "{1, 2, 3}";
            q2 = db.CreateQuery<int>(esql);
            foreach (var item in q2)
                Console.WriteLine(item);


            // db.Categories.Where(o => o is Category).ToArray();
        }

        [TestMethod]
        public void In1()
        {

            var esql = "select o from Orders as o where o.OrderId in { 1, 2 ,3 } ";
            db.CreateQuery<Order>(esql).Execute();

        }

        [TestMethod]
        public void In2()
        {
            var esql = @"select o from Orders as o where o.OrderId in 
                                  ( select value d.OrderId from OrderDetails as d ) ";

            db.CreateQuery<Order>(esql).Execute();

        }

        [TestMethod]
        public void In3()
        {
            var q = db.CreateQuery<int>("select value d.OrderId from OrderDetails as d ");
            var orders = db.Orders.Where(o => ((IEnumerable<int>)q).Contains(o.OrderID)).ToArray();
        }

        [TestMethod]
        public void In4()
        {
            var q = db.CreateQuery<int>("select value d.OrderId from OrderDetails as d ");
            var orders = db.Orders.Where("OrderId in @0", q).ToArray();
        }

        [TestMethod]
        public void InnerJoin1()
        {
            var q1 = db.Orders.Join(db.OrderDetails, o => o.OrderID, d => d.OrderID, (o, d) => new { o, d })
                       .Select(o => new { o.o.OrderID, o.d.UnitPrice });

            //new MyVisitor().Visit(q1.Expression);
            //q1.Execute();

            //var q3 = db.Orders.Join(db.OrderDetails, o => o.OrderID, d => d.OrderID, (o, d) => new { o, d })
            //     .GroupBy(a => a.o).Select(a => new { Order = a.Key, OrderDetails = a.Select(b=>b.d) });

            var q2 = db.Orders.GroupJoin(db.OrderDetails, o => o.OrderID, d => d.OrderID, (o, d) => new { o, d });

            var esql = @"select o.OrderId, d.UnitPrice 
                         from Orders as o Inner Join 
                              OrderDetails as d on o.OrderId == d.OrderId";

            //ef.CreateQuery<object>(esql).Execute(MergeOption.NoTracking);

            db.CreateQuery<object>(esql).Execute();
        }

        [TestMethod]
        public void InnerJoin2()
        {
            var q1 = db.Orders.Join(db.OrderDetails, o => o.OrderID, d => d.OrderID, (o, d) => new { o, d })
                              .Join(db.Products, d => d.d.ProductID, p => p.ProductID, (a, p) => new { a.o, a.d, p })
                              .Select(o => o.o.OrderID);

            var esql = @"select o.OrderId, d.ProductId, p.UnitPrice 
                         from Orders as o 
                              Inner Join OrderDetails as d on o.OrderId == d.OrderId
                              Inner Join Products as p on d.ProductId == p.ProductId";

            // ef.CreateQuery<DbDataRecord>(esql).Execute(MergeOption.NoTracking);

            var q2 = db.CreateQuery<object>(esql);
            q2.Execute();
        }

        [TestMethod]
        public void InnerJoin3()
        {
            var esql = @"select o.OrderId 
                         from Orders as o 
                              join OrderDetails as d on o.OrderId == d.OrderId";

            //ef.CreateQuery<DbDataRecord>(esql).Execute(MergeOption.NoTracking);

            var q2 = db.CreateQuery<object>(esql);
            q2.Execute();
        }

        [TestMethod]
        public void LeftJoin1()
        {
            db.Orders.GroupJoin(db.OrderDetails, o => o.OrderID, o => o.OrderID, (o, x) => new { o, x })
                     .SelectMany(t => t.x.DefaultIfEmpty(), (a, d) => new { a.o.OrderID, d.UnitPrice })
                     .Execute();

            var esql = @"select o.OrderId, d.UnitPrice 
                                     from Orders as o 
                                          left join OrderDetails as d on o.OrderId == d.OrderId";

            var q2 = db.CreateQuery(esql);
            q2.Execute();
        }

        [TestMethod]
        public void LeftJoin2()
        {

            db.Orders.GroupJoin(db.OrderDetails, o => o.OrderID, d => d.OrderID, (o, x) => new { o, x })
                     .SelectMany(t => t.x.DefaultIfEmpty(), (t, d) => new { t.o, d })
                     .GroupJoin(db.Products, o => o.d.ProductID, p => p.ProductID, (t, x) => new { t.d, t.o, x })
                     .SelectMany(t => t.x.DefaultIfEmpty(), (t, p) => new { t.o, t.d, p })
                     .Select(t => new { t.o.OrderID })
                     .Execute();

            var esql = @"select o.OrderId 
                         from Orders as o 
                              left join OrderDetails as d on o.OrderId == d.OrderId
                              left join Products as p on d.ProductId == p.ProductId";

            var q2 = db.CreateQuery(esql);
            q2.Execute();
        }

        [TestMethod]
        public void LeftJoin3()
        {
            var esql = @"select o.OrderId, c.CategoryId, p.ProductId 
                         from Orders as o 
                              left join OrderDetails as d on o.OrderId == d.OrderId
                              left join Products as p on d.ProductId == p.ProductId
                              left join Categories as c on p.CategoryId == c.CategoryId";

            //ef.CreateQuery<DbDataRecord>(esql).Execute(MergeOption.NoTracking);

            var q2 = db.CreateQuery<object>(esql);
            q2.Execute();
        }

        [TestMethod]
        public void OfType()
        {
            string esql;
            IQueryable<Product> q;

            esql = "select p from Products as p where p is of Product";
            q = db.CreateQuery<Product>(esql);
            q.Execute();

            esql = "select p from Products as p where p is not of Product";
            q = db.CreateQuery<Product>(esql);
            q.Execute();
        }

        [TestMethod]
        public void IsNull()
        {
            var esql = "select p from Products as p where p.Category is null";
            var q = db.CreateQuery<Product>(esql);
            q.Execute();

            esql = "select p from Products as p where p.Category is not null";
            q = db.CreateQuery<Product>(esql);
            q.Execute();

            esql = "select p from Products as p where p.Category not is null";
            q = db.CreateQuery<Product>(esql);
            q.Execute();
        }

        #endregion

        [TestMethod]
        public void IndexerField()
        {
            var esql = @"select o.OrderId, d.ProductId, p.UnitPrice 
                         from Orders as o 
                              Inner Join OrderDetails as d on o.OrderId == d.OrderId
                              Inner Join Products as p on d.ProductId == p.ProductId";


            var q2 = db.CreateQuery<IDataRecord>(esql);
            var q3 = q2.Select(o => new { OrderId = (int)o["OrderID"], ProductId = (int)o["ProductId"] })
                       .Where(o => o.OrderId > 1000)
                       .Take(10);


            q3.Execute();

        }
    }

    public class Person2
    {
        public int EmployeeID { get; set; }

        public string FirstName { get; set; }

        //public string LastName { get; set; }
        public string LastName;
    }
}
